package com.scuec.trainingmanagementsys.mapper;

import com.scuec.trainingmanagementsys.entity.Discussion;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface DiscussionMapper {

    @Select("SELECT " +
            "d.DiscussionID as discussionId, " +
            "d.DiscussionContent as discussionContent, " +
            "d.TeacherID as teacherId, " +
            "d.StudentID as studentId, " +
            "d.CreateTime as createTime, " +
            "COALESCE(t.TeacherName, s.StudentName) as userName, " +
            "CASE " +
            "WHEN d.TeacherID IS NOT NULL THEN 'teacher' " +
            "WHEN d.StudentID IS NOT NULL THEN 'student' " +
            "ELSE 'unknown' " +
            "END as userType " +
            "FROM discussion d " +
            "LEFT JOIN teacher t ON d.TeacherID = t.TeacherID " +
            "LEFT JOIN student s ON d.StudentID = s.StudentID " +
            "ORDER BY d.CreateTime DESC")
    List<Discussion> getAllDiscussions();

    @Select("SELECT " +
            "d.DiscussionID as discussionId, " +
            "d.DiscussionContent as discussionContent, " +
            "d.TeacherID as teacherId, " +
            "d.StudentID as studentId, " +
            "d.CreateTime as createTime, " +
            "COALESCE(t.TeacherName, s.StudentName) as userName, " +
            "CASE " +
            "WHEN d.TeacherID IS NOT NULL THEN 'teacher' " +
            "WHEN d.StudentID IS NOT NULL THEN 'student' " +
            "ELSE 'unknown' " +
            "END as userType " +
            "FROM discussion d " +
            "LEFT JOIN teacher t ON d.TeacherID = t.TeacherID " +
            "LEFT JOIN student s ON d.StudentID = s.StudentID " +
            "WHERE d.DiscussionID = #{discussionId}")
    Discussion getDiscussionById(Integer discussionId);

    @Delete("DELETE FROM discussion WHERE DiscussionID = #{discussionId}")
    int deleteDiscussion(Integer discussionId);

    // 修改插入语句的字段名（驼峰转下划线）
    @Insert("INSERT INTO discussion (DiscussionContent, TeacherID, StudentID, CreateTime) " +
            "VALUES (#{discussionContent}, #{teacherId}, #{studentId}, #{createTime})")
    int insertDiscussion(Discussion discussion);

    @Update("UPDATE discussion " +
            "SET discussion_content = #{discussionContent}, " +
            "teacher_id = #{teacherId}, " +
            "student_id = #{studentId}, " +
            "create_time = #{createTime} " +
            "WHERE discussion_id = #{discussionId}")
    int updateDiscussion(Discussion discussion);

    // 根据学生的 studentNumber 查询讨论
    @Select("SELECT " +
            "d.DiscussionID as discussionId, " +
            "d.DiscussionContent as discussionContent, " +
            "d.TeacherID as teacherId, " +
            "d.StudentID as studentId, " +
            "d.CreateTime as createTime, " +
            "COALESCE(t.TeacherName, s.StudentName) as userName, " +
            "CASE " +
            "WHEN d.TeacherID IS NOT NULL THEN 'teacher' " +
            "WHEN d.StudentID IS NOT NULL THEN 'student' " +
            "ELSE 'unknown' " +
            "END as userType " +
            "FROM discussion d " +
            "LEFT JOIN teacher t ON d.TeacherID = t.TeacherID " +
            "LEFT JOIN student s ON d.StudentID = s.StudentID " +
            "WHERE s.StudentNumber = #{studentNumber}")
    List<Discussion> findByStudentNumber(@Param("studentNumber") String studentNumber);

    // 根据学生ID查询讨论
    @Select("SELECT " +
            "d.DiscussionID as discussionId, " +
            "d.DiscussionContent as discussionContent, " +
            "d.TeacherID as teacherId, " +
            "d.StudentID as studentId, " +
            "d.CreateTime as createTime, " +
            "COALESCE(t.TeacherName, s.StudentName) as userName, " +
            "CASE " +
            "WHEN d.TeacherID IS NOT NULL THEN 'teacher' " +
            "WHEN d.StudentID IS NOT NULL THEN 'student' " +
            "ELSE 'unknown' " +
            "END as userType " +
            "FROM discussion d " +
            "LEFT JOIN teacher t ON d.TeacherID = t.TeacherID " +
            "LEFT JOIN student s ON d.StudentID = s.StudentID " +
            "WHERE d.StudentID = #{studentId}")
    List<Discussion> findByStudentId(String studentId);

    /**
     * 根据教师编号查询讨论（注解实现，与其他方法格式统一）
     */
    @Select("SELECT " +
            "d.DiscussionID as discussionId, " +
            "d.DiscussionContent as discussionContent, " +
            "d.TeacherID as teacherId, " +
            "d.StudentID as studentId, " +
            "d.CreateTime as createTime, " +
            "COALESCE(t.TeacherName, s.StudentName) as userName, " +
            "CASE " +
            "WHEN d.TeacherID IS NOT NULL THEN 'teacher' " +
            "WHEN d.StudentID IS NOT NULL THEN 'student' " +
            "ELSE 'unknown' " +
            "END as userType " +
            "FROM discussion d " +
            "LEFT JOIN teacher t ON d.TeacherID = t.TeacherID " +
            "LEFT JOIN student s ON d.StudentID = s.StudentID " +
            "WHERE t.TeacherNumber = #{teacherNumber}")
    List<Discussion> findDiscussionsByTeacherNumber(String teacherNumber);

    /**
     * 教师添加讨论（注解实现，与其他方法格式统一）
     */
    @Insert("INSERT INTO discussion (DiscussionContent, TeacherID, CreateTime) " +
            "VALUES (#{discussionContent}, #{teacherId}, #{createTime})")
    int insertDiscussionByTeacher(Discussion discussion);
}
